# import xlwt
#
# workbook = xlwt.Workbook()
# worksheet = workbook.add_sheet('My sheet')
# # 合并第0行的第0列到第3列。
# worksheet.write_merge(0, 0, 0, 3, 'First Merge')
# font = xlwt.Font()
# font.blod = True
#
# pattern_top = xlwt.Pattern()
# pattern_top.pattern = xlwt.Pattern.SOLID_PATTERN
# pattern_top.pattern_fore_colour = 5
#
# style = xlwt.XFStyle()
# style.font = font
# style.pattern = pattern_top
#
# # 合并第1行到第2行的第0列到第3列。
# worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style)
#
# worksheet.write_merge(4, 6, 3, 6, 'My merge', style)
#
# workbook.save('E:\\test\\xls_xlwt\Merge_cell.xls')
#

import xlsxwriter
import pandas as pd
import numpy as np

def data_process():

    excel1 = pd.read_excel('daydaytable.xls')

    len1 = excel1.shape[0]

    temp_data = excel1.groupby('台号')['违规时长'].agg([len, np.sum])

    taihao_arry = [
        21, 22, 23, 24, 25, 26, 26, 28,  # 【0：8】
        31, 32, 33, 34, 35, 36, 37, 38,  # [8:16]
        41, 42, 43, 44, 45, 46, 47, 48,  # [16:24]
        51, 52, 53, 54, 55, 56, 57, 58,  # [24:32]
        61, 62, 63, 64, 65, 66, 67, 68,  # [32:40]
        71, 72, 73, 74, 75, 76, 77, 78,  # [40:48]
        91, 92, 93, 94, 95, 96, 97, 98,  # [48:56]
    ]

    # print('{}{}'.format('范围测试 21-28',taihao_arry[0:8]))
    data_arry = []

    # print(temp_data.loc[21].values)  #[ 4  7040]
    # print(list(temp_data.loc[21].values)) #[4, 7040]


    for i in taihao_arry:
        try:
            data_arry.append(list(temp_data.loc[i].values))
        except:
            data_arry.append([0, 0])


    ## todo: 收集 接通率和早释率
    #output:  data_arry1 = [[99.9%,1.7%],[99.9%,1.7%],[99.9%,1.7%],[99.9%,1.7%],[99.9%,1.7%],[99.9%,1.7%],[99.9%,1.7%],]


    return  data_arry





data_arry = data_process()


print(data_arry)


# workbook = xlsxwriter.Workbook("testcss.xlsx")
workbook = xlsxwriter.Workbook("testcss.xlsx")
worksheet = workbook.add_worksheet(name="1")
# 大标题 红头 16 xxx日报表
style_tile = workbook.add_format({
    # "fg_color": "yellow",  # 单元格的背景颜色
    "bold": 1,  # 字体加粗
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    "font_color": "red",  # 字体颜色
    "font_size":16
    # "font-family"

})

#副标题  红头 14  202104xxx
style_subtile = workbook.add_format({
    # "fg_color": "yellow",  # 单元格的背景颜色
    "bold": 1,  # 字体加粗
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    "font_color": "red",  # 字体颜色
    "font_size": 14

    # "font-family"

})

## 行属性  绿色背景 加粗    序号   地区  等等
style_rowtitle = workbook.add_format({
    "fg_color": "#98FB98",  # 单元格的背景颜色
    "bold": 1,  # 字体加粗
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    # "font_color": "red"  # 字体颜色
    'border': 1,
    # "font-family"

})

### 绿色背景单元格
style_green_bg = workbook.add_format({

    "fg_color": "#98FB98",
    # "bold": 1,  # 字体加粗
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    'border': 1,

})

### 黄色背景单元格
style_yellow_bg = workbook.add_format({

    "fg_color": "#FFFF00",
    # "bold": 1,  # 字体加粗
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    'border': 1,

})

###  数据标红、加粗,原背景色为绿色
style_red_bold_gbg = workbook.add_format({
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    'border': 1,
    "font_color": "red",  # 字体颜色
    "fg_color": "#98FB98",
    "bold": 1,  # 字体加粗

})


### 数据标红、加粗，原背景色为黄色
style_red_bold_ybg = workbook.add_format({
    "align": "center",  # 对齐方式
    "valign": "vcenter",  # 字体对齐方式
    'border': 1,
    "font_color": "red",  # 字体颜色
    "fg_color": "#FFFF00",
    "bold": 1,  # 字体加粗

})

worksheet.merge_range('A1:O3',data='全市110接警台运行情况日报表',cell_format=style_tile)
worksheet.merge_range('A4:O4',data='(20210404)',cell_format=style_subtile)
# worksheet.write(0, 0, "20210404日报表", style)

worksheet.write('A5','序号',style_rowtitle)
worksheet.write('B5','地区',style_rowtitle)
worksheet.write('C5','接线率',style_rowtitle)
worksheet.write('D5','早释率',style_rowtitle)
worksheet.write('E5','IP电话',style_rowtitle)
worksheet.write('F5','电话链路',style_rowtitle)

worksheet.merge_range('G5:O5',data='违规操作（空摘机）',cell_format=style_rowtitle )


#序号
worksheet.merge_range('A6:A8',data=1,cell_format=style_yellow_bg)
worksheet.merge_range('A9:A11',data=2,cell_format=style_green_bg)
worksheet.merge_range('A12:A14',data=3,cell_format=style_yellow_bg)
worksheet.merge_range('A15:A17',data=4,cell_format=style_green_bg)
worksheet.merge_range('A18:A20',data=5,cell_format=style_yellow_bg)
worksheet.merge_range('A21:A23',data=6,cell_format=style_green_bg)
worksheet.merge_range('A24:A26',data=7,cell_format=style_yellow_bg)

#地区
worksheet.merge_range('B6:B8',data='响水',cell_format=style_yellow_bg)
worksheet.merge_range('B9:B11',data='滨海',cell_format=style_green_bg)
worksheet.merge_range('B12:B14',data='阜宁',cell_format=style_yellow_bg)
worksheet.merge_range('B15:B17',data='射阳',cell_format=style_green_bg)
worksheet.merge_range('B18:B20',data='建湖',cell_format=style_yellow_bg)
worksheet.merge_range('B21:B23',data='东台',cell_format=style_green_bg)
worksheet.merge_range('B24:B26',data='大丰',cell_format=style_yellow_bg)

###通过 标签纸序列方法获取对应的接线率和早释率
#接线率
worksheet.merge_range('C6:C8',data='',cell_format=style_yellow_bg)
worksheet.merge_range('C9:C11',data='',cell_format=style_green_bg)
worksheet.merge_range('C12:C14',data='',cell_format=style_yellow_bg)
worksheet.merge_range('C15:C17',data='',cell_format=style_green_bg)
worksheet.merge_range('C18:C20',data='',cell_format=style_yellow_bg)
worksheet.merge_range('C21:C23',data='',cell_format=style_green_bg)
worksheet.merge_range('C24:C26',data='',cell_format=style_yellow_bg)

#早释率
worksheet.merge_range('D6:D8',data='',cell_format=style_yellow_bg)
worksheet.merge_range('D9:D11',data='',cell_format=style_green_bg)
worksheet.merge_range('D12:D14',data='',cell_format=style_yellow_bg)
worksheet.merge_range('D15:D17',data='',cell_format=style_green_bg)
worksheet.merge_range('D18:D20',data='',cell_format=style_yellow_bg)
worksheet.merge_range('D21:D23',data='',cell_format=style_green_bg)
worksheet.merge_range('D24:D26',data='',cell_format=style_yellow_bg)

#IP电话
worksheet.merge_range('E6:E8',data='正常',cell_format=style_yellow_bg)
worksheet.merge_range('E9:E11',data='正常',cell_format=style_green_bg)
worksheet.merge_range('E12:E14',data='正常',cell_format=style_yellow_bg)
worksheet.merge_range('E15:E17',data='正常',cell_format=style_green_bg)
worksheet.merge_range('E18:E20',data='正常',cell_format=style_yellow_bg)
worksheet.merge_range('E21:E23',data='正常',cell_format=style_green_bg)
worksheet.merge_range('E24:E26',data='正常',cell_format=style_yellow_bg)
#电话链路
worksheet.merge_range('F6:F8',data='正常',cell_format=style_yellow_bg)
worksheet.merge_range('F9:F11',data='正常',cell_format=style_green_bg)
worksheet.merge_range('F12:F14',data='正常',cell_format=style_yellow_bg)
worksheet.merge_range('F15:F17',data='正常',cell_format=style_green_bg)
worksheet.merge_range('F18:F20',data='正常',cell_format=style_yellow_bg)
worksheet.merge_range('F21:F23',data='正常',cell_format=style_green_bg)
worksheet.merge_range('F24:F26',data='正常',cell_format=style_yellow_bg)


###台号 次数  超时时长

## 响水
worksheet.write('G6','台号',style_yellow_bg)
worksheet.write('G7','次数',style_yellow_bg)
worksheet.write('G8','总时长',style_yellow_bg)

for i in range(8):
    # worksheet.write('H6',91+i,style_yellow_bg)
    if data_arry[48+i][0]>20 or data_arry[48+i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '6', 91 + i, style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '7', data_arry[48 + i][0], style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '8', data_arry[48 + i][1], style_red_bold_ybg)
    else:
        worksheet.write(chr(ord('H')+i)+'6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'7',data_arry[48+i][0],style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'8',data_arry[48+i][1],style_yellow_bg)

## 滨海
worksheet.write('G9','台号',style_green_bg)
worksheet.write('G10','次数',style_green_bg)
worksheet.write('G11','总时长',style_green_bg)

for i in range(8):
    if data_arry[16+i][0]>20 or data_arry[16+i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '9', 41 + i, style_red_bold_gbg)
        worksheet.write(chr(ord('H') + i) + '10', data_arry[16 + i][0], style_red_bold_gbg)
        worksheet.write(chr(ord('H') + i) + '11', data_arry[16 + i][1], style_red_bold_gbg)

    else:
        # worksheet.write('H6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'9',41+i,style_green_bg)
        worksheet.write(chr(ord('H')+i)+'10',data_arry[16+i][0],style_green_bg)
        worksheet.write(chr(ord('H')+i)+'11',data_arry[16+i][1],style_green_bg)


##  阜宁
worksheet.write('G12','台号',style_yellow_bg)
worksheet.write('G13','次数',style_yellow_bg)
worksheet.write('G14','总时长',style_yellow_bg)

for i in range(8):
    if data_arry[40+i][0]>20 or data_arry[40+i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '12', 71 + i, style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '13', data_arry[40 + i][0], style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '14', data_arry[40 + i][1], style_red_bold_ybg)

    else:
        # worksheet.write('H6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'12',71+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'13',data_arry[40+i][0],style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'14',data_arry[40+i][1],style_yellow_bg)



## 射阳
worksheet.write('G15','台号',style_green_bg)
worksheet.write('G16','次数',style_green_bg)
worksheet.write('G17','总时长',style_green_bg)

for i in range(8):
    if data_arry[i][0]>20 or data_arry[i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '15', 21 + i, style_red_bold_gbg)
        worksheet.write(chr(ord('H') + i) + '16', data_arry[i][0], style_red_bold_gbg)
        worksheet.write(chr(ord('H') + i) + '17', data_arry[i][1], style_red_bold_gbg)
    else:
        # worksheet.write('H6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'15',21+i,style_green_bg)
        worksheet.write(chr(ord('H')+i)+'16',data_arry[i][0],style_green_bg)
        worksheet.write(chr(ord('H')+i)+'17',data_arry[i][1],style_green_bg)



## 建湖
worksheet.write('G18','台号',style_yellow_bg)
worksheet.write('G19','次数',style_yellow_bg)
worksheet.write('G20','总时长',style_yellow_bg)

for i in range(8):
    if data_arry[32+i][0]>20 or data_arry[32+i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '18', 61 + i, style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '19', data_arry[32 + i][0], style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '20', data_arry[32 + i][1], style_red_bold_ybg)
    else:
        # worksheet.write('H6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'18',61+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'19',data_arry[32+i][0],style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'20',data_arry[32+i][1],style_yellow_bg)


## 东台
worksheet.write('G21','台号',style_green_bg)
worksheet.write('G22','次数',style_green_bg)
worksheet.write('G23','总时长',style_green_bg)


for i in range(8):
    if data_arry[24+i][0]>20 or data_arry[24+i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '21', 51 + i, style_red_bold_gbg)
        worksheet.write(chr(ord('H') + i) + '22', data_arry[24 + i][0], style_red_bold_gbg)
        worksheet.write(chr(ord('H') + i) + '23', data_arry[24 + i][1], style_red_bold_gbg)
    else:
        # worksheet.write('H6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'21',51+i,style_green_bg)
        worksheet.write(chr(ord('H')+i)+'22',data_arry[24+i][0],style_green_bg)
        worksheet.write(chr(ord('H')+i)+'23',data_arry[24+i][1],style_green_bg)

# 注意此处的手动表粗加红的样式会覆盖之前的单元格样式   导致背景色消失
# worksheet.write(chr(ord('H') + 3) + '21', 51 + 3, style_red_bold_gbg)
# worksheet.write(chr(ord('H') + 3) + '22', data_arry[24 + i][0], style_red_bold_gbg)
# worksheet.write(chr(ord('H') + 3) + '23', data_arry[24 + i][1], style_red_bold_gbg)

## 大丰
worksheet.write('G24','台号',style_yellow_bg)
worksheet.write('G25','次数',style_yellow_bg)
worksheet.write('G26','总时长',style_yellow_bg)

for i in range(8):
    if data_arry[8+i][0]>20 or data_arry[8+i][1]>2000:
        worksheet.write(chr(ord('H') + i) + '24', 31 + i, style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '25', data_arry[8 + i][0], style_red_bold_ybg)
        worksheet.write(chr(ord('H') + i) + '26', data_arry[8 + i][1], style_red_bold_ybg)
    else:
        # worksheet.write('H6',91+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'24',31+i,style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'25',data_arry[8+i][0],style_yellow_bg)
        worksheet.write(chr(ord('H')+i)+'26',data_arry[8+i][1],style_yellow_bg)







###特定数据红体加粗



###todo: 将超过 20次数 及超过2000时长的 数据标红加粗



#保存退出工作表
workbook.close()


# temp_excel = pd.read_excel('testcss.xls')